package bl;
 
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Properties;
 
public class DBConnection {
 
    private static DBConnection instance = null;
    public static Connection conn = null;
    private String label, brand, content_, content_2, unit = null;
    public static final String DATE_FORMAT_NOW = "yyyy-MM-dd HH:mm:ss";
    public static StringBuffer onStockBuffer = new StringBuffer();
    
    Properties prop = new Properties();
 
    public DBConnection() {
        
    	
        try {
        	
        	prop.load(new FileInputStream("db.properties"));

        	//db.properties auslesen
        	String dbHost = prop.getProperty("host");
        	String dbPort = prop.getProperty("port");
        	String database = prop.getProperty("database");
        	String dbUser = prop.getProperty("dbuser");
        	String dbPassword = prop.getProperty("dbpassword");

            Class.forName("com.mysql.jdbc.Driver");

            //Verbindung mit DB aufbauen
            conn = DriverManager.getConnection("jdbc:mysql://" + dbHost + ":"
                    + dbPort + "/" + database + "?" + "user=" + dbUser + "&"
                    + "password=" + dbPassword);
        } catch (ClassNotFoundException e) {
            System.out.println("Treiber nicht gefunden");
        } catch (SQLException e) {
            System.out.println("Verbindung zur SQL Datenbank konnte nicht hergestellt werden");
        } catch (IOException e) {
    		e.printStackTrace();
    	}
    }
 
    public static DBConnection getInstance()
    {
        if(instance == null)
            instance = new DBConnection();
        return instance;
    }

    public String getProduct (String code) throws IOException{
    	String product=null;
    	System.out.println(code);
    	Parser parser = new Parser();
    	if(conn != null)
        {
        	Statement query;
            try {
                query = conn.createStatement();
 
                // Ergebnistabelle erzeugen und abholen.
                
                ResultSet result = query.executeQuery("SELECT P_Label, P_Brand, P_Content_, P_Content_2, U_Desc_short " +
                		"FROM products INNER JOIN units	ON (products.P_U_ID = units.U_ID) WHERE P_Barcode = '" + code + "'"); 
                
                while (result.next()) {
                	label = result.getString("P_label");
                	brand = result.getString("P_brand");
                	content_ = result.getString("P_content_");
                	content_2 = result.getString("P_content_2");
                	unit = result.getString("U_Desc_short");
                		
                }
                product = parser.sendProductDescription(code, label, brand, content_, content_2, unit);
                
            } catch (SQLException e) {
            	System.out.print("debug");
                e.printStackTrace();
            }
        }

    	return product;
    }
    public String getOutOfStockProduct (String actual_amount, String code) throws IOException{

    String product=null;
    System.out.println(code);
    Parser parser = new Parser();
    
    if(conn != null)
    {
    	Statement query;
    	try {
    		query = conn.createStatement();
    		
    		// Ergebnistabelle erzeugen und abholen.
    		
    		ResultSet result = query.executeQuery("SELECT P_Label, P_Brand, P_Content_, P_Content_2, U_Desc_short " +
    				"FROM products INNER JOIN units	ON (products.P_U_ID = units.U_ID) WHERE P_Barcode = '" + code + "'"); 
    		
    		while (result.next()) {
    			label = result.getString("P_label");
    			brand = result.getString("P_brand");
    			content_ = result.getString("P_content_");
    			content_2 = result.getString("P_content_2");
    			unit = result.getString("U_Desc_short");
    			
    		}
    		product = parser.parseOnStockProduct(actual_amount, code, label, brand, content_, content_2, unit);
    		
    	} catch (SQLException e) {
    		System.out.print("debug");
    		e.printStackTrace();
    	}
    }
    
    return product;
}
    
    public String getAmountActual (int h_id, String code) throws IOException{
    	new Parser();
    	String amount_actual = null;
    	if(conn != null)
    	{
    		Statement query;
    		try {
    			query = conn.createStatement();
    			
    			// Ergebnistabelle erzeugen und abholen.
    			
    			ResultSet result = query.executeQuery("SELECT IS_amount_actual " +
    					"FROM in_stock WHERE IS_H_ID = '" + h_id + "' AND IS_P_Barcode = '" + code + "'"); 
    			
    			while (result.next()) {
    				 amount_actual = result.getString("IS_amount_actual");
    				
    			}
    			
    		} catch (SQLException e) {
    			System.out.print("debug");
    			e.printStackTrace();
    		}
    	}
    	
    	return amount_actual;
    }
    public String getList (User user) throws IOException{
    	String h_id=null;
    	String[] barcodes;
    	Parser parser = new Parser();
    	StringBuffer barcodeBuffer = new StringBuffer();
    	StringBuffer productBuffer = new StringBuffer();
    	if(conn != null)
    	{
    		Statement query;
    		try {
    			query = conn.createStatement();
    			
				ResultSet sql = query.executeQuery("SELECT H_ID FROM HOMEDROID.household WHERE H_Username = '" + user.getUsername() +"'");
    			
                while (sql.next()) {
                	h_id = sql.getString("H_ID");
                }
                
    			// Ergebnistabelle erzeugen und abholen.
    			
    			ResultSet result = query.executeQuery("SELECT IS_P_Barcode, IS_amount_actual FROM in_stock WHERE IS_H_ID = '" + h_id + "'"); 
    			
    			while (result.next()) {
    				barcodeBuffer.append(result.getString("IS_P_Barcode") + "," +
    						result.getString("IS_amount_actual") + ":" );
    			}
    			barcodes = barcodeBuffer.toString().split(":");
    			
    			for(int i = 0; i < barcodes.length; i++){
    				String[] code = barcodes[i].split(",");
    					
    				ResultSet products = query.executeQuery("SELECT P_Label, P_Brand, P_Content_, P_Content_2, U_Desc_short " +
                    		"FROM products INNER JOIN units	ON (products.P_U_ID = units.U_ID) WHERE P_Barcode = '" + code[0] + "'"); 
    				while(products.next()){
    					
    					productBuffer.append(":" + code[1] + "," + code[0] + "," +
    					products.getString("P_Label") + "," + 
    					products.getString("P_Brand") + "," + 
    					products.getString("P_Content_") + "," + 
    					products.getString("P_Content_2") + "," + 
    					products.getString("U_Desc_short"));

    				}
    			}
    			
    			
    		} catch (SQLException e) {
    			System.out.print("debug");
    			e.printStackTrace();
    		}
    	}
    	
    	return parser.getList(productBuffer);
    }
    
    
    public String getFormatedList(User user) throws IOException{
    	String h_id=null;
    	String[] barcodes;
    	StringBuffer barcodeBuffer = new StringBuffer();
    	StringBuffer productBuffer = new StringBuffer();
    	if(conn != null)
    	{
    		Statement query;
    		try {
    			query = conn.createStatement();
    			
    			ResultSet sql = query.executeQuery("SELECT H_ID FROM HOMEDROID.household WHERE H_Username = '" + user.getUsername() +"'");
    			
    			while (sql.next()) {
    				h_id = sql.getString("H_ID");
    			}
    			
    			// Ergebnistabelle erzeugen und abholen.
    			
    			ResultSet result = query.executeQuery("SELECT IS_P_Barcode, IS_amount_actual FROM in_stock WHERE IS_H_ID = '" + h_id + "'"); 
    			
    			while (result.next()) {
    				barcodeBuffer.append(result.getString("IS_P_Barcode") + "," +
    						result.getString("IS_amount_actual") + ":" );
    			}
    			barcodes = barcodeBuffer.toString().split(":");
    			
    			for(int i = 0; i < barcodes.length; i++){
    				String[] code = barcodes[i].split(",");
    				
    				ResultSet products = query.executeQuery("SELECT P_Label, P_Brand, P_Content_, P_Content_2, U_Desc_short " +
    						"FROM products INNER JOIN units	ON (products.P_U_ID = units.U_ID) WHERE P_Barcode = '" + code[0] + "'"); 
    				while(products.next()){
    					
    					productBuffer.append(":" + code[1] + "," + code[0]);
    					
    				}
    			}
    			
    			
    		} catch (SQLException e) {
    			System.out.print("debug");
    			e.printStackTrace();
    		}
    	}
    	
    	return productBuffer.toString();
    }
    
    public int registerUser(String username,String password)
    {
    	int result = 0;
    	System.out.println("--> Registration");
        if(conn != null)
        {
            // Anfrage-Statement erzeugen.
        	Statement query;
            try {
                query = conn.createStatement();
 
                //1 = Statement erfolgreich, 0 = Statement fehlerhaft abgesetzt
                result = query.executeUpdate("INSERT INTO HOMEDROID.Household (H_Username, H_Password ) VALUES ('" +
        				username + "','" +
        				password + "')");
                
                
            } catch (SQLException e) {
            	result = -2;
            	//e.printStackTrace();
                
            }
        }

		return result;
    }
    public String loginUser(String username)
    {
    	if(conn != null)
    	{
    		// Anfrage-Statement erzeugen.
    		Statement query;
    		try {
    			query = conn.createStatement();
    			
    			//1 = Statement erfolgreich, 0 = Statement fehlerhaft abgesetzt
    			ResultSet sql = query.executeQuery("SELECT H_Password FROM HOMEDROID.Household WHERE H_Username='" + username + "'");
                while (sql.next()) {
                	String dbPW = sql.getString("H_Password");
                	return dbPW;
                }
    			
    			
    		} catch (SQLException e) {
    			return null;
    			
    		}
    	}
    	
    	return null;
    }
    public int addProduct(Product product)
    {
    	int result = 0;
    	System.out.println("--> Add Product");
        if(conn != null)
        {
            // Anfrage-Statement erzeugen.
        	Statement query;
            try {
                query = conn.createStatement();
                System.out.println("addProduct: " + product.getLabel());
                //1 = Statement erfolgreich, 0 = Statement fehlerhaft abgesetzt
                result = query.executeUpdate("INSERT INTO HOMEDROID.Products (P_Barcode, P_Label, P_Brand, P_Content_, P_Content_2, P_U_ID ) VALUES ('" +
        				product.getBarcode() + "','" +
        				product.getLabel() + "','" +
        				product.getBrand() + "','" +
        				product.getContent_() + "','" +
        				product.getContent_2() + "','" +
        				product.getU_id() + "')");
                
                
            } catch (SQLException e) {
            	result = -2;
            	//e.printStackTrace();
                
            }
        }

		return result;
    }
    public int addToStock(StringBuffer buffer, String username, String password) throws IOException
    {
    	int result = 0,h_id = 0;
    	System.out.println("--> Add to Stock");
    	if(conn != null)
    	{
    		// Anfrage-Statement erzeugen.
    		Statement query;
    		String[] items = buffer.toString().split(":");
    		for(int i = 1; i < items.length; i++){
    			System.out.println(items[i]);
    			try {
    				query = conn.createStatement();
    				ResultSet sql = query.executeQuery("SELECT H_ID FROM HOMEDROID.household WHERE H_Username = '" + username +"'");
                    while (sql.next()) {
                    	h_id = sql.getInt("H_ID");

                    }
                    String[] content = items[i].split(",");
//                    System.out.println("??: " + (getAmountActual(h_id, content[2]) == null));
                    String actual_amount = getAmountActual(h_id, content[2]);
                    System.out.println("Actua_amount: " + actual_amount);
                    if(actual_amount == null){
                    	
                    	query  = conn.createStatement();
                    	
                    	query.executeUpdate("INSERT INTO HOMEDROID.in_stock (IS_H_ID, IS_P_Barcode, IS_amount_actual, IS_amount_target, IS_Date_actual) VALUES ('" +
                    			h_id + "','" +
                    			content[2] + "','" +
                    			content[1] + "','" +
                    			0 + "','" +
                    			now() + "')");
                    }else{
                    	result = -1;
                    	onStockBuffer.append(getOutOfStockProduct(actual_amount, content[2]));
                    	
                    	System.out.println("onstockbuffer: " + onStockBuffer);
                    }
    			
    			
    		} catch (SQLException e) {
    			result = -2;
    			//e.printStackTrace();
    			
    		} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    		}
    	}
    	
    	return result;
    }
    public int outOfStock(StringBuffer buffer, String username, String password)
    {
    	int result = 0,h_id = 0;
    	System.out.println("--> Out of Stock");
    	if(conn != null)
    	{
    		// Anfrage-Statement erzeugen.
    		Statement query;
    		String[] items = buffer.toString().split(":");
    		for(int i = 1; i < items.length; i++){
    			System.out.println(items[i]);
    			try {
    				query = conn.createStatement();
    				ResultSet sql = query.executeQuery("SELECT H_ID FROM HOMEDROID.household WHERE H_Username = '" + username +"'");
    				while (sql.next()) {
    					h_id = sql.getInt("H_ID");
    					
    				}
    				String[] content = items[i].split(",");
    				
    				query  = conn.createStatement();
    				
    				//1 = Statement erfolgreich, 0 = Statement fehlerhaft abgesetzt
    				result = query.executeUpdate("DELETE FROM HOMEDROID.in_stock WHERE IS_H_ID = '" + h_id + "' AND IS_P_Barcode = '" + content[2] + "'");
    				
    				
    			} catch (SQLException e) {
    				result = -2;
    				//e.printStackTrace();
    				
    			}
    		}
    	}
    	
    	return result;
    }
    
    public static String now() {
        Calendar cal = Calendar.getInstance();
        SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_NOW);
        return sdf.format(cal.getTime());

      }
    
}